#!/usr/bin/env python
# encoding: utf-8
# @author: liusir
# @file: excel_utils.py
# @time: 2021/7/4 11:42 上午

# 读写excel操作封装

import xlrd3

class ExcelUtils:
    def __init__(self,excel_file_path,sheet_name):
        self.excel_file_path = excel_file_path
        self.sheet_name = sheet_name
        self.sheet = self.get_sheet()

    def get_sheet(self):
        work_book = xlrd3.open_workbook( self.excel_file_path )
        sheet = work_book.sheet_by_name( self.sheet_name )
        return sheet

    def get_row_count(self):  # 获取表格的总行数
        row_count = self.sheet.nrows
        return row_count

    def get_column_count(self):  # 获取表格的总列数
        column_count = self.sheet.ncols
        return column_count

    def get_merged_cell_value(self,row_index, col_index):
        for (min_row_index, max_row_index, min_col_index, max_col_index) in self.sheet.merged_cells:
            if row_index >= min_row_index and row_index < max_row_index:
                if col_index >= min_col_index and col_index < max_col_index:
                    cell_value = self.sheet.cell_value(min_row_index, min_col_index)
                    break
                else:
                    cell_value = self.sheet.cell_value(row_index, col_index)
            else:
                cell_value = self.sheet.cell_value(row_index, col_index)
        return cell_value

    def get_excel_data_by_list(self):
        ''' 把excel转换成如下格式：
            [{'列1名称':列1的值,'列2名称':列2的值,'列3名称':列3的值..},
            {'列1名称':列1的值,'列2名称':列2的值,'列3名称':列3的值..},
            .....]
        '''
        excel_data = []
        first_row_data = self.sheet.row_values(0)
        for row_index in range(1, self.get_row_count()):
            row_dict = {}
            for col_index in range(self.get_column_count()):
                row_dict[first_row_data[col_index]] = self.get_merged_cell_value(row_index, col_index)
            excel_data.append(row_dict)
        return excel_data

if __name__=='__main__':
    import os
    excel_path = os.path.join( os.path.dirname(os.path.abspath(__file__)),'..','samples','test_data.xlsx' )
    excel_utils = ExcelUtils(excel_path,'Sheet1')
    # print( excel_utils.get_row_count() )
    # print( excel_utils.get_column_count() )
    # print( excel_utils.get_merged_cell_value(3,0) )
    # print( excel_utils.get_merged_cell_value(1,3) )
    print( excel_utils.get_excel_data_by_list() )

    # excel_path = os.path.join( os.path.dirname(os.path.abspath(__file__)),'..','test_data','testcase_infos.xlsx' )
    # excel_utils = ExcelUtils(excel_path,'Sheet1')
    # for case_step in excel_utils.get_excel_data_by_list():
    #     print( case_step )
